﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class CrimesBLL


    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public void insert(string cr_name, string record_status,int created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@cr_name", cr_name);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@created_by", created_by);
                dbManager.AddParameters(3, "@created_when", DateTime.Today);
           
  
                string sql;


                sql = " insert into hrm_dc_crimes(cr_name,record_status,created_by,created_when) "
+ " values(@cr_name,@record_status,@created_by,@created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);    

            }

            catch(Exception ex)
             {

                 throw new Exception(ex.Message);  
            }

          finally 
                 {
                     dbManager.Dispose(); 

            }

        }


        public DataTable Select()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT "
+ " hrm_dc_crimes.cr_name,"
+ " mas_record_statuses.record_status_name as status,"
+ " hrm_dc_crimes.record_status,"
+ " hrm_dc_crimes.cr_seq"
+ " FROM" 
+ " hrm_dc_crimes"
+ " Inner Join mas_record_statuses ON mas_record_statuses.record_status = hrm_dc_crimes.record_status"
+ " where hrm_dc_crimes.record_status not like '" + SystemInfo._DeleteRecordStatus  + "'";
                    

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }


        public DataTable SelectStatus()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select * from mas_record_statuses  "
                    + " where record_status in('" + SystemInfo._ActiveRecordStatus  + "','" + SystemInfo._CancelRecordStatus  + "')";
             
                DataSet ds = new DataSet();
                DataTable dt;
                                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();  
            }

        }


        public void Update(int cr_seq,string cr_name, string record_status, int updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@cr_name", cr_name);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@cr_seq", cr_seq);
                dbManager.AddParameters(3, "@updated_by", updated_by);
                dbManager.AddParameters(4, "@updated_when", DateTime.Today);


                string sql;
                

                sql = " update hrm_dc_crimes "
         + "set cr_name=@cr_name,record_status=@record_status,updated_by=@updated_by,updated_when=@updated_when "
         + " where cr_seq=@cr_seq";


   

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {
            }

            finally
            {
                dbManager.Dispose();

            }

        }

 
        public void Delete(int cr_seq,int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@cr_seq", cr_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;
               

                sql = " update hrm_dc_crimes set "
              + " record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"
              + " where cr_seq = @cr_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        ////////// Crimes Dtls /////////////



        public DataTable SelectDtls()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT "
+ " hrm_dc_crime_dtls.crd_name,"
+ " hrm_dc_crimes.cr_name,"
+ " mas_record_statuses.record_status_name,"
+ " hrm_dc_crime_dtls.crd_seq,"
+ " mas_record_statuses.record_status,"
+ " hrm_dc_crimes.cr_seq"
+ " FROM"
+ " hrm_dc_crime_dtls"
+ " Inner Join hrm_dc_crimes ON hrm_dc_crimes.cr_seq = hrm_dc_crime_dtls.cr_seq"
+ " Inner Join mas_record_statuses ON hrm_dc_crime_dtls.record_status = mas_record_statuses.record_status"

+ " where hrm_dc_crime_dtls.record_status not like '" + SystemInfo._DeleteRecordStatus + "'";


//+ " where hrm_dc_crimes.record_status not like '" + SystemInfo._DeleteRecordStatus + "'";


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }


        public DataTable SelectComboCrime()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select * from HRM_DC_CRIMES  "
                    + " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }


        public void InsertDtls(int cr_seq, string crd_name,string status, string created_by)

    {
        try
        {
            dbManager.Open();
            dbManager.CreateParameters(5);

            dbManager.AddParameters(0, "@cr_seq", cr_seq);
            dbManager.AddParameters(1, "@crd_name", crd_name);
            dbManager.AddParameters(2, "@record_status",status );
            dbManager.AddParameters(3, "@created_by", created_by);
            dbManager.AddParameters(4, "@created_when", DateTime.Today);


            string sql;


            sql = " insert into hrm_dc_crime_dtls "
                + " (cr_seq,crd_name,record_status,created_by,created_when) "
+ " values(@cr_seq,@crd_name,@record_status,@created_by,@created_when)";

            dbManager.ExecuteNonQuery(CommandType.Text, sql);

        }

        catch (Exception ex)
        {

            throw new Exception(ex.Message);
        }

        finally
        {
            dbManager.Dispose();

        }


    }

        public void UpdateDtls(int crd_seq,int cr_seq, string crd_name, string status, string updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@cr_seq", cr_seq);
                dbManager.AddParameters(1, "@crd_name", crd_name);
                dbManager.AddParameters(2, "@record_status", status);
                dbManager.AddParameters(3, "@updated_by", updated_by);
                dbManager.AddParameters(4, "@updated_when", DateTime.Today);
                dbManager.AddParameters(5, "@crd_seq", crd_seq);


                string sql;


                sql = " update hrm_dc_crime_dtls "
                + " set cr_seq=@cr_seq,"
                + " crd_name=@crd_name,"
                + " record_status=@record_status,"
                + " updated_by=@updated_by,"
                + " updated_when=@updated_when"
                + " where crd_seq = @crd_seq";


                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }


        }


        public void DeleteDtls(int crd_seq, int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@crd_seq", crd_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;


                sql = " update hrm_dc_crime_dtls set "
              + " record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"
              + " where crd_seq = @crd_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }


     

   
    }
}
